﻿using MetroDataQuery.Model;
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace MetroDataQuery.Utility
{
    public class MetroDataBaseUtility
    {
        /// <summary>
        /// 根据PrjId，增加项目所对应的所有监测点表，当已存在时不会做任何操作。
        /// </summary>
        /// <param name="prjId">项目编号</param>
        public static void AddMonitorPointsTables(int prjId)
        {
            var suffix = (prjId - 1) / 20 + 1;
            MetroDataBaseUtility.CreateTablesBySuffix(suffix);
        }

        /// <summary>
        /// 根据项目编号以及其监测类型获取项目所指向的监测表
        /// </summary>
        /// <param name="prjId"></param>
        /// <param name="monitorType"></param>
        /// <param name="isResult"></param>
        /// <returns></returns>
        public static string GetTableNameByMonitorType(int prjId, string monitorType, bool isResult = false)
        {
            using (var db = new MetroProjectsEntities())
            {
                var suffix = (prjId - 1) / 20 + 1;

                var codeM = db.MonitorTypes.
                    SingleOrDefault(p => p.MonitorType == monitorType);
                var code = codeM == null ? "" : codeM.MonitorTypeCode;

                return String.Format("{0}{1}Points{2}",
                    code,
                    isResult ? "Result" : "",
                    suffix);
            }
        }




        /// <summary>
        /// 按照数字后缀创建所有的水平，垂直分表
        /// </summary>
        /// <param name="suffix"></param>
        private static void CreateTablesBySuffix(int suffix)
        {
            //需要创建的table项
            var dict = new List<string> { "Points", "ResultPoints", "ReviewResultPoints" };

            using (var db = new MetroProjectsEntities())
            {
                var MonitorTypeCodeList = db.MonitorTypes.Select(p => p.MonitorTypeCode).ToList();
                foreach (var typeCode in MonitorTypeCodeList)
                {
                    foreach (var tableKey in dict)
                    {
                        var tableName = string.Format("{0}{1}{2}", typeCode, tableKey, suffix.ToString());
                        MetroDataBaseUtility.CreateTableByNameIfNotExist(tableName);
                    }
                }
            }
        }

        /// <summary>
        /// 根据表名创建测点表
        /// </summary>
        /// <param name="tableName"></param>
        public static void CreateTableByNameIfNotExist(string tableName)
        {
            if (MetroDataBaseUtility.IsTableExist(tableName) == false)
            {
                using (var db = new MonitorProjectsEntities())
                {
                    var sql = String.Format(@"CREATE TABLE [dbo].[{0}](
	                    [PrjId] [int] NULL,
	                    [Name] [varchar](50) NULL,
	                    [Value] [float] NULL,
	                    [DateTime] [datetime] NULL,
	                    [Identity] [int] IDENTITY(1,1) NOT NULL,
	                    [Remark] [varchar](50) NULL,
	                    [No] [int] NULL,
                         CONSTRAINT [PK_{1}] PRIMARY KEY CLUSTERED 
                        (
	                        [Identity] ASC
                        )WITH (PAD_INDEX = OFF, 
                        STATISTICS_NORECOMPUTE = OFF, 
                        IGNORE_DUP_KEY = OFF,
                        ALLOW_ROW_LOCKS = ON, 
                        ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
                        ) ON [PRIMARY]",
                        tableName,
                        tableName);
                    db.Database.ExecuteSqlCommand(sql);
                }
            }
        }

        /// <summary>
        /// 是否存在表
        /// </summary>
        /// <param name="tableName"></param>
        /// <returns></returns>
        public static bool IsTableExist(string tableName)
        {
            using (var db = new MonitorProjectsEntities())
            {
                var exists = db.Database
                     .SqlQuery<int?>(@"
                         IF EXISTS (SELECT * FROM sys.tables WHERE name = @p0) 
                        SELECT 1
                        ELSE
                        SELECT 0", tableName)
                     .SingleOrDefault();
                return exists.Value == 1;
            }
        }

    }
}
